package ch.zbw.pra.sysventory.database.jdbcTemplate;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.mysql.jdbc.Statement;

import ch.zbw.pra.sysventory.database.mapper.ProcessorMapper;
import ch.zbw.pra.sysventory.model.Processor;

/**
 * Greift auf die Datenbank zu (DB-Tabelle Processor) und delegiert das Mapping der Tabellenspalten an den RowMapper
 * Klasse kann auf die Datenbank schreiben, lesen und loeschen
 * 
 * @author Roberto Lucas
 */

public class ProcessorJDBCTemplate implements Serializable {

	private static final long serialVersionUID = -759235616332794695L;
	private static Logger LOG = LoggerFactory.getLogger(ProcessorJDBCTemplate.class);
	private JdbcTemplate jdbcTemplateObject;

	private static final String INSERT_SQL = "INSERT INTO Processor (procName, procFamily,procCores,procClock,proc_compID_fk) VALUES(?,?,?,?,?)";
	
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplateObject = new JdbcTemplate(dataSource);
	}
	
	public Integer insertProcessor(final Processor processor, final Integer computerId) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try{
		jdbcTemplateObject.update(new PreparedStatementCreator() { 
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { 
				PreparedStatement ps = connection.prepareStatement(INSERT_SQL, Statement.RETURN_GENERATED_KEYS); 
					ps.setString(1, processor.getNameProcessor()); 
					ps.setString(2, processor.getFamilyProcessor());
					ps.setInt(3, processor.getCoresProcessor());
					ps.setInt(4, processor.getClockProcessor()); 
					ps.setInt(5, computerId);
					return ps; 
				} 
			}, keyHolder);
		} catch(DataAccessException e){
			LOG.debug("Failed to insert Processor for ComputerId "+computerId);
			LOG.error(e.toString());
		}
		LOG.debug("Inserted processor with id: " + keyHolder.getKey());
		return keyHolder.getKey().intValue();
	}
	
	public List<Processor> listProcessors() {
		String SQL = "SELECT * FROM Processor GROUP BY procname,procFamily,procCores,procClock";
		List<Processor> processors = new ArrayList<Processor>();
		try {
			processors = jdbcTemplateObject.query(SQL,
					new ProcessorMapper());
		} catch (DataAccessException e) {
			LOG.debug("Failed to load a list of Processors");
			LOG.error(e.toString());
		}
		LOG.debug("Loaded a list of Processors");
		return processors;
	}
	
	public Processor readProcessorById(Integer processorId){
		String SQL = "SELECT * FROM Processor WHERE procID_pk = ?";
	    Processor processor = new Processor();
		try {
			processor = jdbcTemplateObject.queryForObject(SQL, 
			                    new Object[]{processorId}, new ProcessorMapper());
		} catch (DataAccessException e) {
			LOG.debug("Failed to read Processor with id " + processorId);
			LOG.error(e.toString());
		}
	      LOG.debug("Read Processor with Id = "+ processor.getProcessorId());
	      return processor;
	}     
	
	public void deleteProcessorById(Integer processorId) {
			String SQL = "DELETE FROM Processor WHERE procID_pk = ?";
			try {
				jdbcTemplateObject.update(SQL, processorId);
			} catch (DataAccessException e) {
				LOG.debug("Failed to delete Processor with id " + processorId);
				LOG.error(e.toString());
			}
			LOG.debug("Deleted Processor with id = " + processorId);
	}

	public Processor readProcessorFromComputer(Integer computerId) {
		String SQL = "SELECT * FROM Processor WHERE proc_compID_fk = ?";
	    Processor processor = new Processor();
		try {
			processor = jdbcTemplateObject.queryForObject(SQL, 
			                    new Object[]{computerId}, new ProcessorMapper());
		} catch (DataAccessException e) {
			LOG.debug("Failed to read Processor where Computer id is " + computerId);
			LOG.error(e.toString());
		}
	    LOG.debug("Read Processor with Id = "+ processor.getProcessorId());
	    return processor;
	}
}


